﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Windows.Forms;
using ExcelToTPI.Utility;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace ExcelToTPI
{
    using System.IO;

    public partial class TpiToExcel : Form
    {
        #region 属性
        private string connectionStringFormat = "Server={0},{1};Database={2};User Id={3};Password={4};";    // SQL Server连接串
        private string connectionString = string.Empty;     // SQLServer连接串
        private string SQLServerTableName = string.Empty;   // 当前选定的SQL Server数据表

        #endregion

        #region 构造函数

        public TpiToExcel()
        {
            InitializeComponent();
            this.BindRelationAndLogicOption();
            backgroundWorker1.WorkerReportsProgress = true;
            backgroundWorker1.WorkerSupportsCancellation = true;
        }

        #endregion

        #region 方法

        private bool CheckSqlServerConnect(string serverIP, int port, string databaseName, string userName, string password)
        {
            bool result = false;
            connectionString = string.Format(connectionStringFormat, serverIP, port, databaseName, userName, password);
            SqlConnection connection = new SqlConnection(connectionString);
            try
            {
                connection.Open();
                result = true;
            }
            catch (Exception ex)
            {

            }
            finally
            {
                connection.Close();
                connection.Dispose();
            }
            return result;
        }

        /// <summary>
        /// 获取 SQL Server 数据表字段
        /// </summary>
        private void BindSqlServerTableList()
        {
            string databaseName = txtSqlServerDatabase.Text.Trim();
            string strSql = string.Format("SELECT Name FROM {0}..SysObjects Where XType='U' ORDER BY Name", databaseName);
            DataSet ds = SqlHelper.ExecuteDataset(connectionString, CommandType.Text, strSql);
            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                this.combSqlServerTable.ValueMember = "Name";
                this.combSqlServerTable.DisplayMember = "Name";
                this.combSqlServerTable.DataSource = dt;
            }
        }

        /// <summary>
        /// 绑定SQL过滤条件下拉选项
        /// </summary>
        private void BindRelationAndLogicOption()
        {
            Dictionary<string, string> dicRelation = new Dictionary<string, string>
                {
                    { "等于", "=" },
                    { "大于", ">" },
                    { "小于", "<" },
                    { "大于等于", ">=" },
                    { "小于等于", "<=" }
                };
            Dictionary<string, string> dicLogic = new Dictionary<string, string>
                {
                    { string.Empty, " " },
                    { "并且", "AND" },
                    { "或者", "OR" }
                };
            DataTable dtRelation = new DataTable();
            dtRelation.Columns.Add("Key");
            dtRelation.Columns.Add("Value");
            DataTable dtLogic = new DataTable();
            dtLogic.Columns.Add("Key");
            dtLogic.Columns.Add("Value");
            foreach (KeyValuePair<string, string> item in dicRelation)
            {
                DataRow dr = dtRelation.NewRow();
                dr["Key"] = item.Key;
                dr["Value"] = item.Value;
                dtRelation.Rows.Add(dr);
            }
            foreach (KeyValuePair<string, string> item in dicLogic)
            {
                DataRow dr = dtLogic.NewRow();
                dr["Key"] = item.Key;
                dr["Value"] = item.Value;
                dtLogic.Rows.Add(dr);
            }
            combRelation.DataSource = dtRelation;
            combRelation.DisplayMember = "Key";
            combRelation.ValueMember = "Value";
            combLogic.DataSource = dtLogic;
            combLogic.DisplayMember = "Key";
            combLogic.ValueMember = "Value";
        }

        /// <summary>
        /// 绑定数据库列名
        /// </summary>
        private void BindTableColumnsList()
        {
            // 获取SQL Server表字段
            string sqlServerTableName = combSqlServerTable.SelectedValue as string;
            string strSql = string.Format("select name from syscolumns where id=object_id('{0}')", sqlServerTableName);
            DataSet dsColumns = SqlHelper.ExecuteDataset(connectionString, CommandType.Text, strSql); // 获取选中SQLServer表字段列表
            // 判断是否获取到字段信息
            if (dsColumns == null || dsColumns.Tables.Count <= 0) return;
            combTpiField.DataSource = dsColumns.Tables[0];
            combTpiField.DisplayMember = "name";
            combTpiField.ValueMember = "name";

            // 显示字段复选列表
            clbColumns.Items.Clear();
            int itemIndex = 0;
            foreach (DataRow fldRow in dsColumns.Tables[0].Rows)
            {
                clbColumns.Items.Add(fldRow["name"]);
                clbColumns.SetItemChecked(itemIndex++, true);
            }
        }

        /// <summary>
        /// 生成KSQL语句
        /// </summary>
        /// <param name="dbCode">数据库代码</param>
        /// <returns></returns>
        private string GenerateKSQL(string dbCode)
        {
            StringBuilder sbSeclectedFldList = new StringBuilder();
            for (int i = 0; i < clbColumns.Items.Count; i++)
            {
                if (clbColumns.GetItemChecked(i))
                {
                    sbSeclectedFldList.Append(clbColumns.GetItemText(clbColumns.Items[i]) + ",");
                }
            }
            if (string.IsNullOrEmpty(sbSeclectedFldList.ToString()))
            {
                sbSeclectedFldList.Append("*");
            }
            string strSql = string.Format("SELECT {0} FROM " + dbCode + " ", sbSeclectedFldList.ToString().TrimEnd(','));
            return strSql;
        }

        #endregion

        #region 事件

        private void Button_ConnectServer_Click(object sender, EventArgs e)
        {
            try
            {
                string serverIP = txtSqlServerIP.Text.Trim();
                if (string.IsNullOrEmpty(txtSqlServerPort.Text))
                {
                    MessageBox.Show("请填写SQL Server 端口号，默认为1433。");
                    return;
                }
                int iPort;
                if (!int.TryParse(txtSqlServerPort.Text, out iPort))
                {
                    MessageBox.Show("端口号格式不正确！");
                    return;
                }
                if (!CheckSqlServerConnect(serverIP, iPort, txtSqlServerDatabase.Text, txtSqlServerUserName.Text,
                    txtSqlServerPassword.Text))
                {
                    MessageBox.Show("SQL Server 数据库连接失败！");

                }
                else
                {
                    MessageBox.Show("SQL Server 数据库连接成功！");
                    this.BindSqlServerTableList();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void Button_SelectExcelSaveFolder_Click(object sender, EventArgs e)
        {
            DialogResult result = this.folderBrowserDialog_ExcelSavePath.ShowDialog();
            if (result == DialogResult.OK)
            {
                string strPath = this.folderBrowserDialog_ExcelSavePath.SelectedPath;
                txtExcelSavePath.Text = strPath;
            }
        }

        private void Button_StartRelate_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(txtExcelSavePath.Text))
            {
                MessageBox.Show("请先选择Excel文件的保存路径！");
                return;
            }
            string strSQL = txtKSQL.Text;
            string condition = string.Empty;
            if (strSQL.IndexOf("WHERE", StringComparison.Ordinal) >= 0)
            {
                condition = strSQL.Substring(strSQL.IndexOf("WHERE", StringComparison.Ordinal));
            }

            /*
            string sumSQL = string.Format("select count(*) from {0} {1}", combSqlServerTable.SelectedValue, condition);
            int iRecordCount = (int)SqlHelper.ExecuteScalar(connectionString, CommandType.Text, sumSQL);
            if (iRecordCount <= 0)
            {
                MessageBox.Show("SQL语句错误或没有符合条件的记录！");
                return;
            }
            */
            int iCapacity = int.Parse(numUpdownCapacity.Text);  // 获取用户指定的Excel容量

            WorkerArgument argument = new WorkerArgument
            {
                DatabaseCode = combSqlServerTable.SelectedValue as string,
                ExcelCapacity = iCapacity,
                ExcelSavePath = txtExcelSavePath.Text,
                RecordCount = Convert.ToInt32(nudRecordCount.Text),
                SqlExpression = strSQL,
                Seperator = "|"
            };
            try
            {
                if (backgroundWorker1.IsBusy != true)
                {
                    // Start the asynchronous operation.
                    backgroundWorker1.RunWorkerAsync(argument);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("导出Excel发生异常 异常信息：" + ex.Message + " 堆栈信息：" + ex.StackTrace);
            }
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            if (backgroundWorker1.WorkerSupportsCancellation)
            {
                // Cancel the asynchronous operation.
                backgroundWorker1.CancelAsync();
            }
        }

        private void TpiToExcel_FormClosing(object sender, FormClosingEventArgs e)
        {
            this.Owner.Show();
        }

        private void btnAddCondition_Click(object sender, EventArgs e)
        {
            string strCondition = string.Format(" {0}='{1}' {2} ", combTpiField.SelectedValue, txtbSearchValue.Text, combLogic.SelectedValue);
            if (!txtKSQL.Text.ToUpper().Contains("WHERE"))
            {
                strCondition = string.Format(" WHERE {0}='{1}' {2} ", combTpiField.SelectedValue, txtbSearchValue.Text, combLogic.SelectedValue);
            }
            txtKSQL.Text += strCondition.Trim(new char[] { ' ', 'A', 'N', 'D', 'O', 'R' });
        }

        private void clbColumns_SelectedIndexChanged(object sender, EventArgs e)
        {
            string strSql = this.GenerateKSQL(combSqlServerTable.SelectedValue.ToString());
            txtKSQL.Text = strSql;
        }

        private void backgroundWorker1_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e)
        {
            BackgroundWorker worker = sender as BackgroundWorker;
            try
            {
                WorkerArgument argument = e.Argument as WorkerArgument;
                if (argument != null)
                {
                    IDataReader reader = SqlHelper.ExecuteReader(connectionString, CommandType.Text, argument.SqlExpression);
                    if (reader != null)
                    {
                        Tools.LargeExport(reader, worker, e, argument);
                    }
                    else
                    {
                        MessageBox.Show("获取数据失败！");
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("导出Excel发生异常 异常信息：" + ex.Message + " 堆栈信息：" + ex.StackTrace);
            }
        }

        private void backgroundWorker1_ProgressChanged(object sender, System.ComponentModel.ProgressChangedEventArgs e)
        {
            this.progressBar1.Value = e.ProgressPercentage;
        }

        private void backgroundWorker1_RunWorkerCompleted(object sender, System.ComponentModel.RunWorkerCompletedEventArgs e)
        {
            MessageBox.Show("导出成功！");
        }

        /// <summary>
        /// 全选状态变化时
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void cbCheckAll_CheckedChanged(object sender, EventArgs e)
        {
            for (int i = 0; i < clbColumns.Items.Count; i++)
            {
                clbColumns.SetItemChecked(i, cbCheckAll.Checked);
            }
            this.txtKSQL.Text = this.GenerateKSQL(combSqlServerTable.SelectedValue as string);
        }
        #endregion

        private void combSqlServerTable_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                if (!string.IsNullOrEmpty(combSqlServerTable.SelectedValue as string))
                {
                    this.BindTableColumnsList();
                    string strSQL = this.GenerateKSQL(combSqlServerTable.SelectedValue.ToString());
                    txtKSQL.Text = strSQL;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }
}